using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
namespace HotelSMSSystem
{
    public class MySQLHelper
    {
        private string _stringConnect;
        private MySqlConnection _connection =null;
        public MySQLHelper(string stringConnect)
        {
            _stringConnect = stringConnect;
            _connection = new MySqlConnection(_stringConnect);
        }
        public string StringConnect
        {
            get { return _stringConnect; }
            set { _stringConnect = value; }
        }
        public void Open()
        {
            //_connection = new MySqlConnection(_stringConnect);
            if (!_connection.State.Equals(ConnectionState.Open))
            {
                try
                {
                    _connection.Open();
                }
                catch (Exception ex)
                {
                    throw new ApplicationException(ex.Message);
                }
            }
        }
        public bool IsOpen()
        {
            bool result = false;
            if (_connection.State.Equals(ConnectionState.Open))
            {
                result = true;
            }
            return result;
        }
        public bool IsClosed()
        {
            bool result = false;
            if (_connection.State.Equals(ConnectionState.Closed))
            {
                result = true;
            }
            return result;
        }
        public DataTable ExecuteQuery(string stringQuery)
        {
            DataTable result = new DataTable();
            MySqlCommand cmd = new MySqlCommand(stringQuery, _connection);
            MySqlDataAdapter da = new MySqlDataAdapter(cmd);
            try
            {
                da.Fill(result);
            }
            catch (Exception ex)
            {
                throw new ApplicationException(ex.Message);
            }
            return result;
        }
        

        public object ExecuteScalar(string stringQuery)
        {
            object result = null;
            MySqlCommand cmd = new MySqlCommand(stringQuery, _connection);
            try
            {
                result = cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw new ApplicationException(ex.Message);
            }
            return result;

        }
        
        public int ExecuteNonQuery(string stringQuery)
        {
            int result = 0;
            MySqlCommand cmd = new MySqlCommand(stringQuery, _connection);
            try
            {
                result=cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new ApplicationException(ex.Message);
            }
            return result;
        }
        public void Close()
        {
            if (!_connection.State.Equals(ConnectionState.Closed))
                _connection.Close();
        }
        public DataSet ExecuteDataSet(string stringQuery)
        {
            DataSet ds = new DataSet();
            MySqlCommand cmd = new MySqlCommand(stringQuery, _connection);
            MySqlDataAdapter da = new MySqlDataAdapter(cmd);
            try
            {
                da.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public void TransactionExecuteNonQuery(string sql)
        {
            MySqlTransaction tran = _connection.BeginTransaction();
            MySqlCommand cmd = new MySqlCommand(sql, _connection, tran);
            try
            {
                cmd.ExecuteNonQuery();
                tran.Commit();
            }
            catch (Exception ex)
            {
                tran.Rollback();
                throw new ApplicationException(ex.Message);
            }

        }
        public void TransactionExecuteNonQuery(string[] sql)
        {
            MySqlTransaction tran = _connection.BeginTransaction();
            bool checkRollback = false;
            try
            {
                foreach (string sub in sql)
                {
                    MySqlCommand cmd = new MySqlCommand(sub, _connection, tran);
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch 
                    {
                        checkRollback = true;
                        break;
                        //throw new ApplicationException(ex.Message);
                    }

                }
            }
            catch (Exception ex)
            {
                checkRollback = true;
                throw new ApplicationException(ex.Message);
            }
            finally
            {
                if (checkRollback == true)
                    tran.Rollback();
                else
                    tran.Commit();
            }
        }
    }
}
